package DBLayer;
import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;


public class DBSupplier
{
	private Connection con;
	public DBSupplier()
	{
	      con = DbConnection1.getInstance().getDBcon();
	}
	
	public ArrayList<Supplier> getAllEmployees(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
	
	public Supplier findSupplier(String supplierId, boolean retriveAssociation)
    {
		String wClause = "  supplierId = '" + supplierId + "'";
        return singleWhere(wClause, retriveAssociation);
    }
	
	public Supplier searchSupplierName(String supplierName, boolean retrieveAssociation){
		String wClause = "name is '%" + supplierName + "%'";
		System.out.println("searchsupplier " + wClause);
		return singleWhere(supplierName, retrieveAssociation);
	}
	
	public int insertSupplier(Supplier supplier) throws Exception
    {  //call to get the next ssn number
        int nextID = GetMax.getMaxId("Select max(supplierId) from employee");
        nextID = nextID + 1;
        System.out.println("next id = " +  nextID);
  
        int rc = -1;
	    String query="INSERT INTO supplier(supplierId, name, address, country, phoneno, email)  VALUES('"+
	  		nextID  + "','"  +
	  		supplier.getName()  + "','"  +
	  		supplier.getAddress() + "','" +
	  		supplier.getCountry()  + "','" +
	  		supplier.getPhoneNo()  + "','" +
	  		supplier.getEmail()  + "')";
                     
        System.out.println("insert : " + query);
        try
        { // insert new employee +  dependent
           Statement stmt = con.createStatement();
           stmt.setQueryTimeout(5);
     	   rc = stmt.executeUpdate(query);
           stmt.close();
        }//end try
       catch(SQLException ex)
       {
          System.out.println("The supplier is not registered");
          throw new Exception ("Supplier is not inserted correct");
       }
       return(rc);
    }
	
	
	public int updateSupplier(Supplier sup)
	{
		Supplier supObj  = sup;
		int rc=-1;

		String query="UPDATE employee SET "+
		 	  "supplierId ='"+ supObj.getSupplierId()+"', "+
		 	  "name ='"+ supObj.getName() + "', " +
              "address ='"+ supObj.getAddress() + "', " +
              "country ='"+ supObj.getCountry() + "' " +
		      "email = '"+ supObj.getEmail() + "'";
              System.out.println("Update query:" + query);
  		try
  		{ // update employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex)
	 	{
	 	 	System.out.println("Update exception in employee db: "+ex);
	  	}
		return(rc);
	}
	
	public int deleteSupplier(String supplierId)
	{
		int rc=-1;
	  	String query="DELETE FROM supplier WHERE supplierId = '" +
		supplierId + "'";
        System.out.println(query);
	  	try
	  	{// delete from employee
	  		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	    catch(Exception ex)
   	    {
	 	System.out.println("Delete exception in employee db: "+ex);
   	    }
		return(rc);
	}
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT supplierId, name, address, country, phoneNo, email";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	
	private ArrayList<Supplier> miscWhere(String wClause, boolean retrieveAssociation)
	{
        ResultSet results;
	    ArrayList<Supplier> list = new ArrayList<Supplier>();	
		String query =  buildQuery(wClause);
  
        try
        { // read the employee from the database
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);
	 	
	
        	while( results.next() )
        	{
        		Supplier supObj = new Supplier();
        		supObj = buildSupplier(results);	
                list.add(supObj);	
        	}//end while
            stmt.close();     
            /*if(retrieveAssociation)
            {   //The supervisor and department is to be build as well
            	for(Employee empObj : list)
            	{
            		String superssn = empObj.getSupervisor().getSsn();
            		Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
            		empObj.setSupervisor(superEmp);
            		System.out.println("Supervisor is seleceted");
                // here the department has to be selected as well
                }
            }*/ //end if   
			
		}//slut try	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	//SingleWhere is used when we only select one employee 	
	private Supplier singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Supplier empObj = new Supplier();
                
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try
		{ // read the employee from the database
			Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() )
	 		{
	 			empObj = buildSupplier(results);
                //assocaition is to be build
                stmt.close();
        /*        if(retrieveAssociation)
                {   //The supervisor and department is to be build as well
                	String superssn = empObj.getSupervisor().getSsn();
                	Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
                	empObj.setSupervisor(superEmp);
                    System.out.println("Supervisor is seleceted");
                    // here the department has to be selected as well
                } */
			}
            else
            { //no employee was found
            empObj = null;
            }
		}//end try	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception: "+e);
	 	}
		return empObj;
	}
	
	private Supplier buildSupplier(ResultSet results)
    {
		Supplier supObj = new Supplier();
		try
        { // the columns from the table Supplier  are used
			supObj.setSupplierId(results.getString("supplierid"));
        	supObj.setName(results.getString("name"));
            supObj.setAddress(results.getString("address"));
            supObj.setCountry(results.getString("country"));
            supObj.setPhoneNo(results.getString("phoneno"));
            supObj.setEmail(results.getString("email"));
        }
		catch(Exception e)
		{
			System.out.println("error in building the employee object");
		}
		return supObj;
    }
}